﻿/*----------------------------------------------------------------
    // Copyright (C) 2009 江苏华招网
    // 版权所有。 
    // 作者：庄金冬 
    //
    // 文件名：DAL_User.cs
    // 文件功能描述：用户数据访问逻辑
    // 创建标识：庄金冬2009-03-06
    //
    // 修改标识：
    // 修改描述：
    //
    // 修改标识：
    // 修改描述：
    //
    // 审核人：
    // 审核时间：
    // 审核说明：
 //----------------------------------------------------------------*/
using System;
using System.Collections.Generic;

using System.Text;
using HZ.Supervise.Model;
using System.Data;
using System.Data.SqlClient;
using HZ.Common;

namespace HZ.Supervise.DAL
{
    /// <summary>
    /// 用户数据访问逻辑
    /// </summary>
    public class DAL_User
    {
        /// <summary>
        /// 获取所有用户（不含权限）
        /// </summary>
        private static string sql_GetAllUser = "SELECT * FROM TB_User";




        /// <summary>
        /// 根据编号获取用户（不包含用户信息，角色信息，权限信息，机构信息）
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <returns></returns>
        public static User GetUserByUserID(string userid)
        {

            // 根据UserID获取单个用户（不包含用户信息，角色信息，权限信息，机构信息）
            string sql_GetUserByUserID = "SELECT TOP 1 * FROM dbo.TB_User WHERE UserID = @UserID";
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@UserID",string.IsNullOrEmpty(userid)?"":userid)
            };
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(CommandType.Text, sql_GetUserByUserID, paras);
            if (dt.Rows.Count > 0)
            {
                return DAL_Helper.GetUserFromDataRow(dt.Rows[0]);
            }
            else
            {
                return null;
            }

        }
        /// <summary>
        /// 新增删除数据
        /// </summary>
        /// <param name="userRuleAdd"></param>
        /// <param name="userRuleDelete"></param>
        /// <returns></returns>
        public static int UpdateUserRule(IList<UserRule> userRuleAdd, IList<UserRule> userRuleDelete)
        {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < userRuleAdd.Count; i++)
            {
                sb.Append("DELETE FROM TB_UserRule where userid='" + userRuleAdd[i].UserID + "'");
                sb.Append("insert into TB_UserRule ([UserID] ,[OperatingRuleID],[IsEffective]) values ('" + userRuleAdd[i].UserID + "','2021',1)");
            }
            for (int i = 0; i < userRuleDelete.Count; i++)
            {
                sb.Append("DELETE FROM TB_UserRule where userid='" + userRuleDelete[i].UserID + "'");
            }
            //执行存储过程
            return HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sb.ToString());
        }
        /// <summary>
        /// 根据编号获取用户（包含用户信息，角色信息，权限信息，机构信息）
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <returns></returns>
        public static User GetUserInfoByUserID(string userid)
        {

            User user = new User();
            //try
            //{
                SqlParameter[] paras = new SqlParameter[1];

                //用户编号
                paras[0] = new SqlParameter("@in_UserID", string.IsNullOrEmpty(userid) ? "" : userid);

                DataSet ds = new DataSet();
                ds = HZ.Data.DbHelperFactory.Create().ExecuteDataSet(CommandType.StoredProcedure, "usp_GetUserInfoByUserID", paras);

                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        user = DAL_Helper.GetUserFromDataRow(ds.Tables[0].Rows[0]);
                        Role role = DAL_Helper.GetRoleFromDataRow(ds.Tables[1].Rows[0]);
                        user.UserRole = role;

                        Org org = DAL_Helper.GetOrgFromDataRow(ds.Tables[2].Rows[0]);
                        user.Org = org;

                        IList<OperatingRule> operatingRuleList = DAL_Helper.GetOperatingRulesFromDataTable(ds.Tables[3]);
                        user.OperatingRuleList = (List<OperatingRule>)operatingRuleList;
                    }
                }

//            }
//            catch (Exception ex)
//            {
//                //执行不成功，记录日志 
//                log4netManager.LogDebug(typeof(DAL_User).FullName, "根据编号获取用户（包含用户信息，角色信息，权限信息，机构信息）不成功", ex);
//#if DEBUG
//                throw ex;
//#endif
//            }

            return user;


        }

        /// <summary>
        /// 获取所有用户
        /// </summary>
        /// <returns>用户列表</returns>
        public static IList<User> GetAllUser()
        {
            IList<User> list;
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql_GetAllUser);
            list = DAL_Helper.GetUsersFromDataTable(dt);
            return list;
        }


        /// <summary>
        /// 用户登录0:登录失败;1:登录成功;2:用户名密码不匹配;3:用户未启用;
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <param name="userpassword">用户密码</param>
        /// <param name="user">(out)用户实例</param>
        /// <returns>0:登录失败;1:登录成功;2:用户名密码不匹配;3:用户未启用;</returns>
        public static int Login(string userid, string userpassword, out User user)
        {
            user = new User();
            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[3];

                //TB_User【UserID,UserName,UserPassword,IsAdmin,IsOnline,IsOnline,IsUsing】
                //用户编号
                paras[0] = new SqlParameter("@in_UserID", string.IsNullOrEmpty(userid) ? "" : userid);
                //密码
                paras[1] = new SqlParameter("@in_UserPassword", string.IsNullOrEmpty(userpassword) ? "" : userpassword);

                //输入参数处理结果(0:登录失败;1:登录成功;2:用户名密码不匹配;3:用户未启用;)
                paras[2] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[2].Direction = ParameterDirection.Output;

                DataSet ds = new DataSet();
                ds = HZ.Data.DbHelperFactory.Create().ExecuteDataSet(CommandType.StoredProcedure, "usp_Login", paras);

                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        user = DAL_Helper.GetUserFromDataRow(ds.Tables[0].Rows[0]);
                        Role role = DAL_Helper.GetRoleFromDataRow(ds.Tables[1].Rows[0]);
                        user.UserRole = role;

                        Org org = DAL_Helper.GetOrgFromDataRow(ds.Tables[2].Rows[0]);
                        user.Org = org;
                    }
                }



                retValue = int.Parse(paras[2].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "添加用户并设置权限不成功", ex);
            }

            return retValue;

        }


        /// <summary>
        /// 用户登录0:登录失败;1:登录成功;2:用户名密码不匹配;3:用户未启用;
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <param name="userpassword">用户密码</param>
        /// <param name="RoleID">用户角色</param>
        /// <param name="user">(out)用户实例</param>
        /// <returns>0:登录失败;1:登录成功;2:用户名密码不匹配;3:用户未启用;</returns>
        public static int Login(string userid, string userpassword, string RoleID, out User user)
        {
            user = new User();
            int retValue = 0;
            string strTemp = @" SELECT * FROM View_RoleUser WHERE  RoleID<>'01'  AND  RoleID<>'02'  AND UserID='{0}' ";
            switch (RoleID.Trim())
            {
                //医疗机构
                case "01":
                    strTemp = @" SELECT * FROM View_RoleUser WHERE  RoleID='01' AND UserID='{0}' ";
                    break;
                //配送企业
                case "02":
                    strTemp = @" SELECT * FROM View_RoleUser WHERE  RoleID='02' AND UserID='{0}' ";
                    break;
                default:
                    break;
            }

            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(string.Format(strTemp, userid));
            if (dt.Rows.Count <= 0)
            {
                return 0;
            }

            try
            {
                SqlParameter[] paras = new SqlParameter[]
                {
                     //TB_User【UserID,UserName,UserPassword,IsAdmin,IsOnline,IsOnline,IsUsing】
                     //用户编号
                    new SqlParameter("@in_UserID", string.IsNullOrEmpty(userid) ? "" : userid),
                     //密码
                    new SqlParameter("@in_UserPassword", string.IsNullOrEmpty(userpassword) ? "" : userpassword),
                    //输入参数处理结果(0:登录失败;1:登录成功;2:用户名密码不匹配;3:用户未启用;)
                    new SqlParameter("@out_success", SqlDbType.Int)
                };

                paras[paras.Length - 1].Direction = ParameterDirection.Output;

                DataSet ds = new DataSet();
                ds = HZ.Data.DbHelperFactory.Create().ExecuteDataSet(CommandType.StoredProcedure, "usp_Login", paras);

                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        user = DAL_Helper.GetUserFromDataRow(ds.Tables[0].Rows[0]);
                        Role role = DAL_Helper.GetRoleFromDataRow(ds.Tables[1].Rows[0]);
                        user.UserRole = role;

                        Org org = DAL_Helper.GetOrgFromDataRow(ds.Tables[2].Rows[0]);
                        user.Org = org;
                    }
                }

                retValue = int.Parse(paras[2].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "用户登陆不成功", ex);
            }

            return retValue;

        }

        /// <summary>
        /// 更新用户信息[名称、是否启用](0,更新失败;1,更新成功;2,用户编号不存在;)
        /// </summary>
        /// <param name="user">用户[名称、是否启用]</param>
        /// <param name="Flag">0更新信息，1锁定/解锁</param>
        /// <returns>(0,更新失败;1,更新成功;2,用户编号不存在;)</returns>
        public static int UpdateUser(User user, int Flag)
        {
            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[5];

                //TB_User【UserID,UserName,IsUsing】
                //用户编号
                paras[0] = new SqlParameter("@in_UserID", string.IsNullOrEmpty(user.UserID) ? "" : user.UserID);
                //用户名称
                paras[1] = new SqlParameter("@in_UserName", string.IsNullOrEmpty(user.UserName) ? "" : user.UserName);
                //是否启用
                paras[2] = new SqlParameter("@in_IsUsing", user.IsUsing ? 1 : 0);
                //标志：0更新信息，1锁定/解锁
                paras[3] = new SqlParameter("@in_Flag", Flag);


                //输入参数处理结果(0,添加失败;1,添加成功;2,用户编号已存在;)
                paras[4] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[4].Direction = ParameterDirection.Output;


                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_User_Update", paras);

                retValue = int.Parse(paras[4].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "更新用户信息用户不成功", ex);

            }

            return retValue;
        }


        /// <summary>
        /// 用户锁定/解锁（批量）(0,失败;1,成功;)
        /// </summary>
        /// <param name="userids">用户编号集合</param>
        /// <param name="IsUsing">0.锁定;1.解锁</param>
        /// <returns>(0,失败;1,成功;)</returns>
        public static int LockOrUnblockUser(string userids, int IsUsing)
        {
            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[3];

                //用户编号集合(多个用,分隔)
                paras[0] = new SqlParameter("@in_UserIDs", string.IsNullOrEmpty(userids) ? "" : userids);
                //是否启用
                paras[1] = new SqlParameter("@in_IsUsing", IsUsing);

                //输入参数处理结果(0,失败;1,成功;)
                paras[paras.Length - 1] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[paras.Length - 1].Direction = ParameterDirection.Output;

                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_User_LockOrUnblock", paras);

                retValue = int.Parse(paras[paras.Length - 1].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "用户锁定/解锁（批量）不成功", ex);

            }

            return retValue;
        }


        /// <summary>
        /// 初始化用户密码（批量）(0,失败;1,成功;)
        /// </summary>
        /// <param name="userids">用户编号集合</param>
        /// <param name="userPassword">用户密码</param>
        /// <returns>(0,失败;1,成功;)</returns>
        public static int ResetUserPassword(string userids, string userPassword)
        {
            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[3];

                //用户编号集合(多个用,分隔)
                paras[0] = new SqlParameter("@in_UserIDs", string.IsNullOrEmpty(userids) ? "" : userids);
                //是否启用
                paras[1] = new SqlParameter("@in_UserPassword", userPassword);

                //输入参数处理结果(0,失败;1,成功;)
                paras[paras.Length - 1] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[paras.Length - 1].Direction = ParameterDirection.Output;

                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_UserPassword_Reset", paras);

                retValue = int.Parse(paras[paras.Length - 1].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "初始化用户密码不成功", ex);

            }

            return retValue;
        }


        /// <summary>
        /// 修改密码(0,更新失败;1,更新成功;2,原密码错误)
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <param name="oldpassword">原始密码</param>
        /// <param name="newpassword">新密码</param>
        /// <param name="falg">0.初始化密码1.更新密码</param>
        /// <returns>0,更新失败;1,更新成功;2,原密码错误</returns>
        public static int UpdatePassword(string userid, string oldpassword, string newpassword, int falg)
        {
            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[5];

                //TB_User【UserID,UserPassword】
                //用户编号
                paras[0] = new SqlParameter("@in_UserID", string.IsNullOrEmpty(userid) ? "" : userid);

                //用户名称
                paras[1] = new SqlParameter("@in_oldUserPassword", string.IsNullOrEmpty(oldpassword) ? "" : oldpassword);

                //是否启用
                paras[2] = new SqlParameter("@in_UserPassword", string.IsNullOrEmpty(newpassword) ? "" : newpassword);

                //是否启用
                paras[3] = new SqlParameter("@in_Flag", falg);

                //输入参数处理结果(0,原密码错误;1,更新成功;2,更新失败)
                paras[4] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[4].Direction = ParameterDirection.Output;


                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_UserPassword_Update", paras);

                retValue = int.Parse(paras[4].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "更新用户密码不成功", ex);

            }

            return retValue;
        }

        /// <summary>
        /// 用户解锁
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <returns></returns>
        public static bool UnblockUser(string userid)
        {
            User user = new User();
            user.UserID = userid;
            user.IsUsing = true;
            return UpdateUser(user, 1) == 1;
        }

        /// <summary>
        /// 用户锁定
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <returns></returns>
        public static bool LockUser(string userid)
        {
            User user = new User();
            user.UserID = userid;
            user.IsUsing = false;
            return UpdateUser(user, 1) == 1;
        }


        /// <summary>
        /// 更新用户信息[名称、是否启用]同时设置权限(0,更新失败;1,更新成功;2,用户编号不存在;)
        /// </summary>
        /// <param name="user">用户[名称、是否启用、权限集合]</param>
        /// <returns>(0,更新失败;1,更新成功;2,用户编号不存在;)</returns>
        public static int UpdateUserRule(User user)
        {
            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[5];

                //TB_User【UserID,UserName,IsUsing】
                //用户编号
                paras[0] = new SqlParameter("@in_UserID", string.IsNullOrEmpty(user.UserID) ? "" : user.UserID);
                //用户名称
                paras[1] = new SqlParameter("@in_UserName", string.IsNullOrEmpty(user.UserName) ? "" : user.UserName);
                //是否启用
                paras[2] = new SqlParameter("@in_IsUsing", user.IsUsing ? 1 : 0);

                //处理权限编号
                StringBuilder sbOperatingRuleIDs = new StringBuilder("");
                string strOperatingRuleIDs = "";
                if (user.OperatingRuleList != null)
                {
                    if (user.OperatingRuleList.Count > 0)
                    {
                        foreach (OperatingRule operatingRule in user.OperatingRuleList)
                        {
                            sbOperatingRuleIDs.Append(operatingRule.OperatingRuleID + ",");
                        }

                        strOperatingRuleIDs = sbOperatingRuleIDs.ToString().Trim().Substring(0, sbOperatingRuleIDs.Length - 1);

                    }
                }

                //
                paras[3] = new SqlParameter("@OperatingRuleIDs", strOperatingRuleIDs);

                //输入参数处理结果(0,添加失败;1,添加成功;2,用户编号已存在;)
                paras[4] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[4].Direction = ParameterDirection.Output;


                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_UserRule_Update", paras);

                retValue = int.Parse(paras[4].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "更新用户信息用户不成功", ex);

            }

            return retValue;
        }

        /// <summary>
        /// 删除用户(0,删除失败;1,删除成功;2,该用户不存在;)
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <returns>(0,删除失败;1,删除成功;2,该用户不存在;)</returns>
        public static int DeleteUser(string userid)
        {
            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[2];

                paras[0] = new SqlParameter("@in_UserID", string.IsNullOrEmpty(userid) ? "" : userid);

                paras[1] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[1].Direction = ParameterDirection.Output;


                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_User_Del", paras);

                retValue = int.Parse(paras[1].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "删除用户不成功", ex);
            }

            return retValue;
        }

        /// <summary>
        /// 获取用户的操作权限
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <returns>操作权限集合</returns>
        public static IList<OperatingRule> GetUserOperatingRule(string userid)
        {
            string sql_temp = @"SELECT     OperatingRuleID, OperatingRuleName, 
                                           Module, ComFlag, CenFlag, HosFlag,ManFlag
                                FROM  dbo.View_UserRule WHERE  [UserID]='{0}' and ManFlag = 1";

            StringBuilder sbWhere = new StringBuilder("");

            if (!string.IsNullOrEmpty(userid))
            {
                sbWhere.AppendFormat(sql_temp, userid); // 增加UserID查询条件                
            }

            IList<OperatingRule> list = null;
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sbWhere.ToString());
            list = DAL_Helper.GetOperatingRulesFromDataTable(dt);
            return list;
        }
        /// <summary>
        /// 获取用户操作权限（2）特殊权限
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public static IList<OperatingRule> GetUserOperatingRuleSpecial(string userid)
        {
            string sql_temp = @"SELECT     OperatingRuleID, OperatingRuleName, 
                                           Module, ComFlag, CenFlag, HosFlag
                                FROM  dbo.View_UserRule WHERE  [UserID]='{0}'and OperatingRuleID in(2022,2023,2025) and manflag = 1";

            StringBuilder sbWhere = new StringBuilder("");

            if (!string.IsNullOrEmpty(userid))
            {
                sbWhere.AppendFormat(sql_temp, userid); // 增加UserID查询条件                
            }

            IList<OperatingRule> list = null;
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sbWhere.ToString());
            list = DAL_Helper.GetOperatingRulesFromDataTable(dt);
            return list;
        }

        /// <summary>
        /// 获取所有权限
        /// </summary>
        /// <returns>所有操作权限集合</returns>
        public static IList<OperatingRule> GetAllOperatingRule()
        {
            string sql_temp = @"SELECT     OperatingRuleID, OperatingRuleName, 
                                           Module, ComFlag, CenFlag, HosFlag
                                FROM  dbo.TB_OperatingRule  and OperatingRuleID<>2021";

            IList<OperatingRule> list = null;
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql_temp);
            list = DAL_Helper.GetOperatingRulesFromDataTable(dt);
            return list;
        }


        /// <summary>
        /// 根据 模块名 操作名 获取权限
        /// </summary>
        /// <param name="Module">模块名</param>
        /// <param name="OperatingRuleName">操作名</param>
        /// <returns>权限</returns>
        public static OperatingRule GetOperatingRule(HZ.Supervise.Model.Enum.Module Module, HZ.Supervise.Model.Enum.OperateType OperateType)
        {
            string sql_temp = @" SELECT     OperatingRuleID, OperatingRuleName, 
                                           Module, ComFlag, CenFlag, HosFlag
                                FROM  dbo.TB_OperatingRule where OperatingRuleName='{0}' and Module='{1}'";

            IList<OperatingRule> list = null;
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(string.Format(sql_temp, OperateType.ToString(), Module.ToString()));
            list = DAL_Helper.GetOperatingRulesFromDataTable(dt);
            return list.Count > 0 ? list[0] : new OperatingRule();
        }

        /// <summary>
        /// 获取所有权限
        /// </summary>
        /// <returns>所有操作权限集合</returns>
        public static IList<HZ.Supervise.Model.View.UserMenuRule> GetUserMenuRule(string userID)
        {
            string sql_temp = @"SELECT  *  FROM  View_UserMenuRule  WHERE  [UserID] ='{0}' 
	                                       AND [RoleType] = (SELECT RoleID 
	                                       FROM dbo.TB_Role_User  
	                                       WHERE  [UserID] ='{1}')";

            IList<HZ.Supervise.Model.View.UserMenuRule> list = null;
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(string.Format(sql_temp, userID, userID));
            list = DAL_Helper.CommonFillList<HZ.Supervise.Model.View.UserMenuRule>(dt);
            return list;
        }

        /// <summary>
        /// 判断某个用户是否有该权限
        /// </summary>
        /// <param name="userID">用户编号</param>
        /// <param name="module">权限模块</param>
        /// <param name="OperatingRuleID">权限ID</param>
        /// <returns></returns>
        public static bool CheckUserRule(string userID, HZ.Supervise.Model.Enum.Module module, HZ.Supervise.Model.Enum.OperateType operateType)
        {
            string sql_temp = @"SELECT  *  FROM  View_UserRule WHERE  [UserID] ='{0}' AND [Module]='{1}' AND [OperatingRuleName]='{2}' ";
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(string.Format(sql_temp, userID, HZ.Supervise.Model.Enum.EnumParse.GetModuleName(module), HZ.Supervise.Model.Enum.EnumParse.GetOperateName(operateType)));

            return dt.Rows.Count > 0;
        }

        /// <summary>
        /// 获取所有权限
        /// </summary>
        /// <returns>所有操作权限集合</returns>
        public static IList<HZ.Supervise.Model.View.UserMenuRule> GetAllUserMenuRule()
        {
            string sql_temp = @"SELECT  DISTINCT 
	                                        '' AS OperatingRuleID,
                                            MenuID,
                                            ParentMenuID,
	                                        MenuName,
	                                        WinPath,
	                                        WebPath,
                                            IcoPath,
	                                        Sort,
                                            RoleType,
	                                        '' as UserID,
	                                        1	as IsEffective  FROM  TB_Menu ";

            IList<HZ.Supervise.Model.View.UserMenuRule> list = null;
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql_temp);
            list = DAL_Helper.CommonFillList<HZ.Supervise.Model.View.UserMenuRule>(dt);
            return list;
        }


        /// <summary>
        /// 获取某个单位所有操作权限菜单
        /// </summary>
        /// <param name="RoleType">菜单所属角色：03.管理机构 02.配送企业01.医疗机构</param>
        /// <returns></returns>
        public static IList<HZ.Supervise.Model.View.UserMenuRule> GetAllUserMenuRule(string RoleType)
        {
            string sql_temp = @"SELECT  DISTINCT 
	                                        '' AS OperatingRuleID,
                                            MenuID,
                                            ParentMenuID,
	                                        MenuName,
	                                        WinPath,
	                                        WebPath,
                                            IcoPath,
	                                        Sort,
                                            RoleType,
	                                        '' as UserID,
	                                        1	as IsEffective  FROM  TB_Menu WHERE [RoleType] ='{0}'";

            IList<HZ.Supervise.Model.View.UserMenuRule> list = null;
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(string.Format(sql_temp, RoleType));
            list = DAL_Helper.CommonFillList<HZ.Supervise.Model.View.UserMenuRule>(dt);
            return list;
        }

        /// <summary>
        /// 条件查询
        /// </summary>
        /// <param name="user">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<User> GetUserByWhere(HZ.Supervise.Model.Query.User user, int currentPage, int pageSize, out int totalRecord)
        {
            string sql_temp = @"SELECT * FROM View_UserRole WHERE 1=1 ";

            StringBuilder sbWhere = new StringBuilder(sql_temp);

            if (user != null)
            {
                if (!string.IsNullOrEmpty(user.Userid))
                {
                    sbWhere.AppendFormat(" AND  [UserID] LIKE '%{0}%'", user.Userid.Trim()); // 增加UserID查询条件
                }
                if (!string.IsNullOrEmpty(user.Username))
                {
                    sbWhere.AppendFormat(" AND  [UserName] LIKE '%{0}%'", user.Username.Trim()); // 增加UserName查询条件
                }
                if (!string.IsNullOrEmpty(user.OrgID))
                {
                    sbWhere.AppendFormat(" AND  [OrgID] = '{0}'", user.OrgID.Trim()); // 增加OrgID查询条件
                }
                if (!string.IsNullOrEmpty(user.RoleID))
                {
                    sbWhere.AppendFormat(" AND  [RoleID] LIKE '%{0}%'", user.RoleID.Trim()); // 增加RoleID查询条件
                }
                if (user.IsAdmin != null)
                {
                    sbWhere.AppendFormat(" AND  [IsUserAdmin] = {0}", (bool)user.IsAdmin ? 1 : 0); // 增加IsUserAdmin是否主用户查询条件
                }
                if (user.Isusing != null)
                {
                    sbWhere.AppendFormat(" AND  [Isusing] = {0}", (bool)user.Isusing ? 1 : 0); // 增加Isusing查询条件
                }
                if (user.Isonline != null)
                {
                    sbWhere.AppendFormat(" AND  [Isonline] =  {0}", (bool)user.Isonline ? 1 : 0); // 增加Isonline查询条件
                }
                if (user.LastlogintimeFrom != null)
                {
                    sbWhere.AppendFormat(" AND  [Lastlogintime] >= '{0}'", user.LastlogintimeFrom.ToString()); // 增加LastlogintimeFrom查询条件
                }

                if (user.LastlogintimeTo != null)
                {
                    sbWhere.AppendFormat(" AND  [Lastlogintime] <= '{0}'", user.LastlogintimeTo.ToString());  // 增加LastlogintimeTo查询条件
                }
                if (user.IsJK != null)
                {
                    sbWhere.AppendFormat(" AND  [IsJK] = {0}", (bool)user.IsJK ? 1 : 0); // 增加Isusing查询条件
                }

            }

            sbWhere.Append(" ORDER BY UserID ");
            IList<User> list;
            totalRecord = HZ.Data.DbHelperFactory.Create().ExecuteTable(sbWhere.ToString()).Rows.Count;
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sbWhere.ToString(), pageSize);
            pdes.CurrentPage = currentPage;
            DataTable dt = pdes.GetSpecailPage();
            list = DAL_Helper.GetUsersFromDataTable(dt);
            return list;
        }
        /// <summary>
        /// 查询出所用有户权限信息
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public static IList<UserRule> GetUserRuleDetailBywhere(string where)
        {
            string sql_temp = @" select * from TB_UserRule where UserID in(" + where + ") ";
            StringBuilder sbWhere = new StringBuilder(sql_temp);
            sbWhere.Append(" ORDER BY UserID ");
            IList<UserRule> list;
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(string.Format(sql_temp));
            return list = DAL_Helper.CommonFillList<HZ.Supervise.Model.UserRule>(dt);
        }
        /// <summary>
        /// 条件查询
        /// </summary>
        /// <param name="user">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<User> GetUserDetailByWhere(string where, int currentPage, int pageSize, out int totalRecord)
        {
            string sql_temp = @"SELECT * FROM View_UserRole WHERE 1=1 " + where;
            StringBuilder sbWhere = new StringBuilder(sql_temp);
            sbWhere.Append(" ORDER BY UserID ");
            IList<User> list;
            totalRecord = HZ.Data.DbHelperFactory.Create().ExecuteTable(sbWhere.ToString()).Rows.Count;
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sbWhere.ToString(), pageSize);
            pdes.CurrentPage = currentPage;
            DataTable dt = pdes.GetSpecailPage();
            list = DAL_Helper.GetUsersFromDataTable(dt);
            return list;
        }


        /// <summary>
        /// 条件机构用户查询（含机构角色相关信息）
        /// </summary>
        /// <param name="user">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<HZ.Supervise.Model.View.OrgUsers> GetOrgUsersByWhere(HZ.Supervise.Model.Query.OrgUsers user, int currentPage, int pageSize, out int totalRecord)
        {
            string where = DAL_Helper.GetWhereCondition<HZ.Supervise.Model.Query.OrgUsers>(user);

            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(string.Format("SELECT * FROM View_OrgUsers WHERE 1=1 {0} ", where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;

            DataTable dt = pdes.GetSpecailPage();
            return DAL_Helper.CommonFillList<HZ.Supervise.Model.View.OrgUsers>(dt);
        }


        /// <summary>
        /// 添加用户同时设置权限(0,添加失败;1,添加成功;2,用户编号已存在;)
        /// </summary>
        /// <param name="user">用户对象（每个用户的UserRole仅RoleID须赋值，Org仅OrgID须赋值）</param>
        /// <param name="ruleIList">操作权限集合（每个权限仅RuleID须赋值）</param>
        /// <returns>(0,添加失败;1,添加成功;2,用户编号已存在;)</returns>
        public static int AddUser_OperatingRule(User user, IList<OperatingRule> ruleIList)
        {
            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[11];

                //TB_User【UserID,UserName,UserPassword,IsAdmin,IsOnline,IsOnline,IsUsing】
                //用户编号
                paras[0] = new SqlParameter("@in_UserID", string.IsNullOrEmpty(user.UserID) ? "" : user.UserID.Trim());
                //用户名称
                paras[1] = new SqlParameter("@in_UserName", string.IsNullOrEmpty(user.UserName) ? "" : user.UserName.Trim());
                //密码
                paras[2] = new SqlParameter("@in_UserPassword", string.IsNullOrEmpty(user.UserPassword) ? "" : user.UserPassword.Trim());
                //是否管理员
                paras[3] = new SqlParameter("@in_IsAdmin", user.IsAdmin ? 1 : 0);
                //是否在线
                paras[4] = new SqlParameter("@in_IsOnline", user.IsOnline ? 1 : 0);
                //是否启用
                paras[5] = new SqlParameter("@in_IsUsing", user.IsUsing ? 1 : 0);

                //TB_Role_User【UserID,RoleID,IsAdmin,OrgID】
                if (user.UserRole != null)
                {
                    if (string.IsNullOrEmpty(user.UserRole.RoleID) || string.IsNullOrEmpty(user.Org.OrgID))
                    {
                        new Exception("用户角色相关信息为空，添加用户信息必须提供用户的角色信息，故系统阻止继续操作！");
                    }

                    //角色编号
                    paras[6] = new SqlParameter("@in_RoleID", string.IsNullOrEmpty(user.UserRole.RoleID) ? "" : user.UserRole.RoleID.Trim());
                    //是否主用户
                    paras[7] = new SqlParameter("@in_IsUserAdmin", user.IsUserAdmin ? 1 : 0);
                    //机构编号
                    paras[8] = new SqlParameter("@in_OrgID", string.IsNullOrEmpty(user.Org.OrgID) ? "" : user.Org.OrgID.Trim());
                }
                else
                {
                    new Exception("用户角色信息为空，添加用户信息必须提供用户的角色信息，故系统阻止继续操作！");
                }

                //处理权限编号
                StringBuilder sbOperatingRuleIDs = new StringBuilder("");
                string strOperatingRuleIDs = "";
                if (ruleIList != null)
                {
                    if (ruleIList.Count > 0)
                    {
                        foreach (OperatingRule operatingRule in ruleIList)
                        {
                            sbOperatingRuleIDs.Append(operatingRule.OperatingRuleID + ",");
                        }

                        strOperatingRuleIDs = sbOperatingRuleIDs.ToString().Trim().Substring(0, sbOperatingRuleIDs.Length - 1);

                    }
                }

                //权限编号集合
                paras[9] = new SqlParameter("@OperatingRuleIDs", strOperatingRuleIDs);

                //输入参数处理结果(0,添加失败;1,添加成功;2,用户编号已存在;)
                paras[10] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[10].Direction = ParameterDirection.Output;


                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_UserRoleRule_Add", paras);

                retValue = int.Parse(paras[10].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "添加用户并设置权限不成功", ex);
            }

            return retValue;
        }

        public static int UpdateUser_IsJK(User user)
        {
            var idb = HZ.Data.DbHelperFactory.Create();
            int count = 0;
            int IsJK = (bool)user.IsJK ? 1 : 0;
            try
            {
                idb.BeginTrans();
                string sql = "update TB_User set IsJK=" + IsJK + " where UserID = '" + user.UserID.Trim() + "'";
                idb.ExecuteNonQuery(sql);
                idb.CommitTrans();
                count = 1;
            }
            catch (Exception ex)
            {
                count = 0;
                idb.RollTrans();
                log4netManager.LogDebug(typeof(DAL_User).FullName, "更新子用户【是否疾控】状态不成功", ex);
            }
            return count;
        }



        /// <summary>
        /// 设置用户的操作权限
        /// </summary>
        /// <param name="userid">用户编号</param>
        /// <param name="ruleIList">操作权限集合</param>
        /// <returns></returns>
        public static bool SetUserOperatingRule(string userid, IList<OperatingRule> ruleIList)
        {
            int retValue = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[3];

                //如果用户编号为空
                if (string.IsNullOrEmpty(userid))
                {
                    new Exception("用户角色信息为空，添加用户信息必须提供用户的角色信息，故系统阻止继续操作！");
                }
                paras[0] = new SqlParameter("@in_UserID", string.IsNullOrEmpty(userid) ? "" : userid.Trim());

                //paras[1] = new SqlParameter("@in_bit", 1);

                //处理权限编号
                StringBuilder sbOperatingRuleIDs = new StringBuilder("");
                string strOperatingRuleIDs = "";
                if (ruleIList != null)
                {
                    if (ruleIList.Count > 0)
                    {
                        foreach (OperatingRule operatingRule in ruleIList)
                        {
                            sbOperatingRuleIDs.Append(operatingRule.OperatingRuleID + ",");
                        }

                        strOperatingRuleIDs = sbOperatingRuleIDs.ToString().Trim().Substring(0, sbOperatingRuleIDs.Length - 1);

                    }
                }

                //
                paras[1] = new SqlParameter("@OperatingRuleIDs", strOperatingRuleIDs);

                paras[2] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[2].Direction = ParameterDirection.Output;


                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_UserRule_Add", paras);

                retValue = int.Parse(paras[2].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "设置用户权限不成功", ex);
            }

            return retValue == 1 ? true : false;

        }

        /// <summary>
        /// 授权(0,失败;1,成功;)
        /// </summary>
        /// <param name="Orgs">机构字典(key:OrgID,value:RoleID)</param>
        /// <param name="flag">启用情况：0未启用；1启用</param>
        /// <returns>(0,失败;1,成功;)</returns>
        public static int AuthorizeOrg(IDictionary<string, string> Orgs, int flag)
        {
            int retValue = 0;

            try
            {
                SqlParameter[] paras = new SqlParameter[4];

                //处理机构编号
                StringBuilder sbOrgIDs = new StringBuilder("");
                string strOrgIDs = "";
                //处理角色编号
                StringBuilder sbRoleIDs = new StringBuilder("");
                string strRoleIDs = "";
                if (Orgs.Count > 0)
                {

                    foreach (var OrgID in Orgs)
                    {
                        sbOrgIDs.Append(OrgID.Key + ",");
                    }
                    strOrgIDs = sbOrgIDs.ToString().Trim().Substring(0, sbOrgIDs.Length - 1);

                    foreach (var OrgID in Orgs)
                    {
                        sbRoleIDs.Append(OrgID.Value + ",");
                    }

                    strRoleIDs = sbRoleIDs.ToString().Trim().Substring(0, sbRoleIDs.Length - 1);

                }

                //权限编号集合
                paras[0] = new SqlParameter("@in_OrgIDs", strOrgIDs);
                //角色编号集合
                paras[1] = new SqlParameter("@in_RoleIDs", strRoleIDs);
                //启用情况：0未启用；1启用
                paras[2] = new SqlParameter("@in_IsUsing", flag);

                //输入参数处理结果(0,添加失败;1,添加成功;)
                paras[3] = new SqlParameter("@out_success", SqlDbType.Int);

                paras[paras.Length - 1].Direction = ParameterDirection.Output;


                //执行存储过程
                HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_AuthorizeOrg", paras);

                retValue = int.Parse(paras[paras.Length - 1].Value.ToString());
            }
            catch (Exception ex)
            {
                //执行不成功，记录日志 
                log4netManager.LogDebug(typeof(DAL_User).FullName, "机构授权操作不成功", ex);
            }

            return retValue;
        }





        public static string GetAdminUserID(string p)
        {
            string sql = string.Format(@"SELECT  tru.UserID FROM dbo.TB_User AS tu INNER JOIN  dbo.TB_Role_User AS tru
ON tu.UserID = tru.UserID WHERE tru.IsAdmin =1 AND tu.UserID = '{0}'", p);
            return HZ.Data.DbHelperFactory.Create().ExecuteScalar(sql).ToString();

        }


        /// <summary>
        /// Autor：曹鹏
        /// TIME：2011年4月7日20:01:29
        /// NOTE：条件机构用户查询（含机构角色相关信息）
        /// </summary>
        /// <param name="user">查询用实体</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <param name="totalRecord">（输出参数）总记录数目</param>
        /// <returns></returns>
        public static IList<HZ.Supervise.Model.View.OrgUsers> GetOrgUsersByWhere2(HZ.Supervise.Model.Query.OrgUsers user, int currentPage, int pageSize, out int totalRecord)
        {
            string where = HZ.Data.DAL_Helper.GetWhereCondition<HZ.Supervise.Model.Query.OrgUsers>(user, true);
            HZ.Data.PageDescribeYn pdes = new HZ.Data.PageDescribeYn(string.Format("SELECT * FROM View_ManUsers WHERE 1=1 AND UserID <> 'zbzx' {0} ", where), pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;
            DataTable dt = pdes.GetSpecailPage();
            return HZ.Data.DAL_Helper.CommonFillList<HZ.Supervise.Model.View.OrgUsers>(dt);
        }
        /// <summary>
        /// 平台基础信息---医院基础信息
        /// </summary> 
        public static DataTable GetHospital(string str, int currentPage, int pageSize, out int totalRecord)
        {
            string SQL = string.Format(@"select * from (select distinct UserID,UserName,Case when IsUsing=1 then '是' when IsUsing=0 then '否'end IsUsing,
            CASE WHEN IsAdmin = 1 THEN '是' when IsAdmin=0 then '否' ELSE '' END IsAdmin,
            ar.AreaName FatherAreaName,h.AreaName AreaName,Levels1  from TB_HospitalInfo v inner join TB_Hospital h on v.HospitalID=h.HospitalID 
            inner join TB_User u on v.HospitalID=u.UserID inner join tb_area ar on SUBSTRING(v.AreaID,0,5)+'00'=ar.AreaID inner join tb_area a on v.AreaID=v.AreaID 
            where IsAdmin = 1 {0} )tb order by UserID", str);
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(SQL, pageSize);
            pdes.CurrentPage = currentPage;
            totalRecord = pdes.TotalCount;
            return pdes.GetSpecailPage(); 
        }

        /// <summary>
        /// 添加登录日志
        /// </summary> 
        public static int AddLoginLog(LoginLog lg)
        {
            HZ.Data.SQLHelper sh = new HZ.Data.SQLHelper();
            try
            {
                sh.BeginTrans();

                string sql = string.Format("insert into TB_LoginLog(ID,[UID],UName,OID,WW,LeiBie,OpTime) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}');",
                    lg.ID, lg.UID, lg.UName, lg.OID, lg.WW, lg.LeiBie, lg.OpTime);
                sh.ExecuteNonQuery(sql);

                sh.CommitTrans();
                return 1;
            }
            catch (Exception ex)
            {
                sh.RollTrans();
                return 0;
            }
        }
    }
}

